package tuneModels;
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Prophet unzip the mysql-connector-java-3.1.11-bin.jar file. Place the
 * com folder (and org) folder in same directory as this file
 */
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;

/**
 *
 * @author Prophet Israel Agyeman-Prempeh
 */
public class Database {

    java.sql.Connection conn;

    /**
     * Default constructor for the database
     */
    public Database() {
    }

    /**
     * This method returns true if the database is connected and false if not
     *
     * @return
     */
    public boolean isConnected() {
        return initComponents();
    }

    /**
     * Initialises connection to the database
     *
     * @return represents successful or unsuccessful connection
     */
    public boolean initComponents() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = java.sql.DriverManager.getConnection(
                    "jdbc:mysql://localhost/tune?user=root&password=");

        } catch (Exception e) {
            System.out.println(e);
            return false;
        }
        return true;
    }

    /**
     * Returns a string to be used to play when a track is selected in the table
     *
     * @param title
     * @param artiste
     * @return
     * @throws java.io.IOException
     */
    public String getPath(String title, String artiste) throws IOException {
        String query = "";
        String truePath = "";
        try {
            query = "SELECT path FROM music WHERE title='" + title + "' AND author='" + artiste + "'";
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            System.out.println("executed");
            result.next();
            String path = result.getString(1);
            truePath = path.replaceAll("%", Pattern.quote("\\"));
            System.out.println("Path to get: " + path);

        } catch (SQLException e) {

        }
        return truePath;
    }

    /**
     * Once the parameters of title and artiste is passed to it it deletes it
     * via the sql statement DELETE FROM music WHERE title='" + title + "' AND
     * author='" + artiste + "'"
     *
     * @param title
     * @param artiste
     * @return
     */
    public boolean delete(String title, String artiste) {
        boolean status = false;
        String query = "";
        try {
            query = "DELETE FROM music WHERE title='" + title + "' AND author='" + artiste + "'";
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            status = true;
        } catch (SQLException e) {

        }
        return status;
    }

    /**
     * This method adds a song to the DB and returns the id of the song You pass
     * a Song class to it and it takes the needed and required details
     *
     * @param song
     * @return the transaction id.
     */
    public int addSong(Song song) {
        String title = song.title();
        String author = song.author();
        String time = song.time();
        String album = song.album();
        String path = song.path().replaceAll(Pattern.quote("\\"), "%");
        String query = "";
        int index = 0;
        try {
            query = ("INSERT INTO music(title,author,time,album,path,play_time,date) "
                    + "VALUES ('" + title + "','" + author + "','" + time + "','" + album + "','" + path + "',CURTIME(),CURDATE())");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            System.out.println("Executed query one");
            query = ("SELECT music_id FROM music ORDER BY music_id DESC LIMIT 1");
            ResultSet result = stmt.executeQuery(query);
            System.out.println("Executed query two");
            result.next();
            index = result.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return index;
    }

    /**
     * Exports the database information into a csv file It takes all the
     * necessary files into a format that can be read by any calculative
     * spreadsheet
     */
    public void export() {
        String filename = "details.csv";
        try {
            FileWriter fw = new FileWriter(filename);
            String query = "select * from music";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                fw.append(rs.getString(1));
                fw.append(',');
                fw.append(rs.getString(2));
                fw.append(',');
                fw.append(rs.getString(3));
                fw.append(',');
                fw.append(rs.getString(4));
                fw.append(',');
                fw.append(rs.getString(5));
                fw.append('\n');
            }
            fw.flush();
            fw.close();
            System.out.println("CSV File is created successfully.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * A method that returns an ArrayList of Suggestions for Search
     *
     * @param wordToSearch
     * @return
     */
    public ArrayList suggestSearch(String wordToSearch) {
        Vector allRows = new Vector();
        Vector row;
        ArrayList<String> search = new ArrayList<>();
        ArrayList rslt = new ArrayList();
        String query = "";
        try {
            query = "SELECT DISTINCT title, author, time, album FROM music WHERE title LIKE'%" + wordToSearch + "%' ORDER BY music_id DESC";
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                row = new Vector();
                String name = result.getString("title");
                if (!search.contains(name)) {
                    search.add(name);
                }
                row.add(result.getString("title"));
                row.add(result.getString("author"));
                row.add((String) convertTime(result.getInt("time")));
                row.add(result.getString("album"));
                allRows.add(row);
            }
            rslt.add(search);
            rslt.add(allRows);

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rslt;
    }

    /**
     * This method returns the current state of the database as a vector This
     * vector is passed to the calling of a table to repopulate it
     *
     * @return represents the items in a row
     */
    public Vector getTableInfo() {
        String query = "";
        Vector<Vector> vector = new Vector<>();
        Vector<Object> items;
        try {
            query = ("SELECT title,author,album,play_time,date FROM music ORDER BY music_id DESC LIMIT 100 ");
            Statement stmt = conn.createStatement();
            ResultSet rslt = stmt.executeQuery(query);
            while (rslt.next()) {
                items = new Vector<>();
                String title = rslt.getString(1);
                String artiste = rslt.getString(2);
                String album = rslt.getString(3);
                Object playTime = rslt.getTime(4);
                Object date = rslt.getDate(5);
                items.add("You played: " + title + "  by: " + artiste + "  From the album: " + album + "  at: " + playTime + "  On: " + date);
                vector.add(items);
            }

        } catch (SQLException e) {
            return vector;
        }
        return vector;
    }

    /**
     * A method used to generate a vector for the table It has a distinct
     * statement in its sql and so does not repeat redundant data
     *
     * @return
     */
    public Vector populateList() {
        String query = "";
        Vector<Vector> vector = new Vector<>();
        Vector<Object> items;
        try {
            query = ("SELECT DISTINCT title, author, time, album FROM music ORDER BY music_id DESC LIMIT 100 ");
            Statement stmt = conn.createStatement();
            ResultSet rslt = stmt.executeQuery(query);
            while (rslt.next()) {
                items = new Vector<>();
                items.add(rslt.getString(1));
                items.add(rslt.getString(2));
                items.add(convertTime(rslt.getInt(3)));
                items.add(rslt.getString(4));
                vector.add(items);
            }

        } catch (SQLException e) {
            return vector;
        }
        return vector;
    }

    /**
     * This method is a helper method that takes raw time value of an mp3 in
     * seconds and converts it into time format min:sec
     *
     * @param val
     * @return
     */
    public String convertTime(int val) {
        int s = val % 60;
        String sec = s + "";
        String min = (val / 60) + "";
        String finalString = (min + ":" + sec);
        return finalString;
    }
}//class

